package org.nobject.common.db;

import static org.nobject.common.lang.StringUtils.isEmpty;

import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.nobject.common.lang.ArrayUtils;
import org.nobject.common.lang.ClassUtils;

/**
 * SQL工具类
 *
 * @author bianrongjun
 * @version 1.0
 */
public class SQLUtils {
	
	/**
	 * 是否为空或者脏数据
	 * 
	 * @param strs 字符串数组
	 * 
	 * @see #isDirty(String)
	 */
	public static boolean isDirtyOrBlank(String... strs) {
		if (strs == null) return true;
		for (int i = 0; i < strs.length; i++) {
			if (isEmpty(strs[i])||isDirty(strs[i])) {
				return true;
			}
		}
		return false;
	}
	
	/**
	 * 是否包含SQL关键字的脏数据
	 * @param str
	 */
	public static boolean isDirty(String str) {
		if (str != null) {
			if (str.indexOf(";") >= 0 ||str.indexOf("%") >= 0||str.indexOf("_") >= 0||str.indexOf("'") >= 0
				|| Pattern.compile("and .*", Pattern.CASE_INSENSITIVE).matcher(str).find() 
				|| Pattern.compile("or .*", Pattern.CASE_INSENSITIVE).matcher(str).find()
			) {
				return true;
			}
		}
		return false;
	}
	
	/** 
	 * 获取IN语句中的问号字串
	 * @param l 问号个数
	 */
	public static String getInQM(int l){
		StringBuffer sb=new StringBuffer();
		for (int i = 0; i < l; i++) {
			if(i>0) sb.append(",");
			sb.append("?");
		}
		return sb.toString();
	}
	
	/** 
	 * 获取IN语句字串
	 * @param strs
	 */
	public static String getIn(String[] strs){
		StringBuffer sb=new StringBuffer();
		for (int i = 0; i < strs.length; i++) {
			if(i!=0) sb.append(",");
			sb.append("'"+strs[i]+"'");	
		}
		return sb.toString();
	}
	
	/** 
	 * 获取IN语句字串
	 * 解决超过1000个项时报的错
	 * @param column
	 * @param strs
	 */
	public static String getIn(String column,String[] strs){
		
		StringBuffer sb=new StringBuffer();
		int l=strs.length;
		int c=l/1000;
		int m=l%1000;
		for(int i=0;i<c;i++){
			if(i!=0) sb.append(" OR ");
			sb.append(column+" IN ("+getIn((String[])ArrayUtils.subArray(strs, i*1000, 1000))+")");
			
		}
		if(m!=0){
			if(sb.length()>0){
				sb.append(" OR ");
			}
			
			sb.append(column+" IN ("+getIn((String[])ArrayUtils.subArray(strs, c*1000, l))+")");
		}
		return sb.toString();
	}
	
	public static void main(String[] args) {
		
		List l=new LinkedList();
		
		for (int i = 0; i < 4101; i++) {
			l.add(i+"");
		}
		System.out.println(getIn("a",(String[])ArrayUtils.toArray(l, String.class)));;
	}
	
	/** 
	 * 获取IN语句字串
	 * @param nums
	 */
	public static String getIn(int[] nums){
		StringBuffer sb=new StringBuffer();
		for (int i = 0; i < nums.length; i++) {
			if(i!=0) sb.append(",");
			sb.append(""+nums[i]+"");	
		}
		return sb.toString();
	}
	
	/** 
	 * 获取IN语句字串
	 * @param nums
	 */
	public static String getIn(long[] nums){
		StringBuffer sb=new StringBuffer();
		for (int i = 0; i < nums.length; i++) {
			if(i!=0) sb.append(",");
			sb.append(""+nums[i]+"");	
		}
		return sb.toString();
	}
	
	/** 
	 * 获取IN语句
	 * @param collection 容器
	 */
	public static String getIn(Collection collection){
		StringBuffer sb=new StringBuffer();
		
		for (Iterator iter = collection.iterator(); iter.hasNext();) {
			Object o = (Object) iter.next();
			
			if(ClassUtils.isNumber(o.getClass())){
				sb.append(o.toString());
			}else{
				sb.append("'"+(String)o+"'");
			}
			
			if(iter.hasNext()) sb.append(",");
		}
		
		return sb.toString();
	}
	
	/**
	 * 过滤脏字符为空
	 * 
	 * @param str 字符串
	 */
	public static String filterDirty(String str) {
		return str.replaceAll("[_\\.;'=% ]", "");
	}
	
	/** 
	 * 获取SQLSelect
	 * @param columns select字段
	 */
	public static String getSqlSelect(String [] columns){
		return getSqlSelect(columns, new HashMap());
	}
	
	/** 
	 * 获取SQLSelect
	 * @param columns select字段
	 * @param pre 字段前缀
	 */
	public static String getSqlSelect(String [] columns,String pre){
		Map m=new LinkedHashMap();
		for(String column:columns){
			m.put(column, pre);
		}
		return getSqlSelect(columns, m);
	}
	
	/** 
	 * 获取SQLSelect
	 * @param columns select字段
	 * @param queryTableColumnMap 字段映射
	 */
	public static String getSqlSelect(String [] columns,Map<String,String> queryTableColumnMap){
		if(columns==null||columns.length==0) columns=new String[]{"*"};
		StringBuffer sqlSelect=new StringBuffer();
		for (int i = 0; i < columns.length; i++) {
			
			String preTab=queryTableColumnMap!=null?queryTableColumnMap.get(columns[i]):null;
			if(!isEmpty(preTab)){
				sqlSelect.append(preTab).append(".");
			}
			sqlSelect.append(columns[i]);
			if(i <( columns.length-1)) sqlSelect.append(",");
		}
		return sqlSelect.toString();
	}
}
